package com.iven.xie.service.impl;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.iven.xie.entity.DbConfig;
import com.iven.xie.entity.DbTable;
import com.iven.xie.entity.DbTableColumn;
import com.iven.xie.entity.MysqlDbColumn;
import com.iven.xie.service.TableService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author xiezhendong
 * @data 2021/7/22
 */
@Service("mySql")
@Slf4j
public class MysqlTableServiceImpl implements TableService {
    @Override
    public List<DbTable> getTableInfoList(DbConfig dbConfig) {
        List<DbTable> tableList = null;
        String sql = "SELECT * FROM information_schema.tables WHERE table_schema = ?";
        try {
            Connection conn = this.getDataSource(dbConfig).getConnection();
            PreparedStatement stmt = this.getPreparedStatement(conn, sql);
            if (stmt != null) {
                stmt.setString(1, dbConfig.getSchema());
                ResultSet rs = stmt.executeQuery();
                if (rs != null) {
                    tableList = Lists.newArrayList();
                    DbTable table = null;
                    while (rs.next()) {
                        table = new DbTable();
                        table.setTableSchema(dbConfig.getSchema());
                        table.setTableName(rs.getString("table_name"));
                        table.setTableComment(rs.getString("table_comment"));
                        table.setCreateTime(rs.getTimestamp("create_time"));
                        tableList.add(table);
                    }
                }
                close(conn, rs, stmt);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableList;
    }

    @Override
    public List<DbTableColumn> getTableColumns(String table, DbConfig dbConfig) {
        List<DbTableColumn> columnList = Lists.newArrayList();
        String sql = "SELECT DISTINCT * FROM information_schema.COLUMNS WHERE table_schema = ? AND table_name = ? ORDER BY ORDINAL_POSITION";
        try {
            Connection conn = this.getDataSource(dbConfig).getConnection();
            PreparedStatement stmt = this.getPreparedStatement(conn, sql);
            if (stmt != null) {
                stmt.setString(1, dbConfig.getSchema());
                stmt.setString(2, table);
                ResultSet rs = stmt.executeQuery();
                if (rs != null) {
                    MysqlDbColumn column = null;
                    while (rs.next()) {
                        column = new MysqlDbColumn();
                        column.setTableSchema(dbConfig.getSchema());
                        column.setTableName(table);
                        column.setColumnName(rs.getString("column_name"));
                        column.setColumnType(rs.getString("column_type"));
                        column.setColumnDefault(rs.getObject("column_default"));
                        column.setColumnKey(rs.getString("column_key"));
                        column.setColumnComment(rs.getString("column_comment"));
                        column.setDataType(rs.getString("data_type"));
                        column.setCharacterMaximumLength(rs.getLong("character_maximum_length"));
                        column.setCharacterOctetLength(rs.getLong("character_octet_length"));
                        column.setExtra(rs.getString("extra"));
                        column.setIsNullable(rs.getString("is_nullable"));
                        column.setPrivileges(rs.getString("privileges"));
                        column.setOrdinalPosition(rs.getInt("ordinal_position"));
                        columnList.add(column);
                    }
                }
                close(conn, rs, stmt);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columnList;
    }

    @Override
    public Map<String, List<DbTableColumn>> getMutilTableColumns(DbConfig dbConfig) {
        Map<String, List<DbTableColumn>> tableMap = Maps.newHashMap();
        String sql = "SELECT DISTINCT * FROM information_schema.COLUMNS WHERE table_schema = ? AND table_name in ("
                + "SELECT table_name FROM information_schema.tables WHERE table_schema = ?"
                + ") ORDER BY table_name, ORDINAL_POSITION";
        try {
            Connection conn = this.getDataSource(dbConfig).getConnection();
            PreparedStatement stmt = getPreparedStatement(conn, sql);
            if (stmt != null) {
                stmt.setString(1, dbConfig.getSchema());
                stmt.setString(2, dbConfig.getSchema());
                ResultSet rs = stmt.executeQuery();
                if (rs != null) {
                    MysqlDbColumn column = null;
                    List<DbTableColumn> columnList = null;
                    String tempName = "";
                    String tname = "";
                    while (rs.next()) {
                        column = new MysqlDbColumn();
                        tname = rs.getString("table_name");
                        if (!tempName.equals(tname)) {
                            if (columnList != null) {
                                tableMap.put(tempName, columnList);
                            }
                            tempName = tname;
                            columnList = Lists.newArrayList();
                        }
                        column.setTableSchema(dbConfig.getSchema());
                        column.setTableName(tname);
                        column.setColumnName(rs.getString("column_name"));
                        column.setColumnType(rs.getString("column_type"));
                        column.setColumnDefault(rs.getObject("column_default"));
                        column.setColumnKey(rs.getString("column_key"));
                        column.setColumnComment(rs.getString("column_comment"));
                        column.setDataType(rs.getString("data_type"));
                        column.setCharacterMaximumLength(rs.getLong("character_maximum_length"));
                        column.setCharacterOctetLength(rs.getLong("character_octet_length"));
                        column.setExtra(rs.getString("extra"));
                        column.setIsNullable(rs.getString("is_nullable"));
                        column.setPrivileges(rs.getString("privileges"));
                        column.setOrdinalPosition(rs.getInt("ordinal_position"));
                        columnList.add(column);
                    }
                    if (columnList != null) {
                        tableMap.put(tempName, columnList);
                    }
                }
                close(conn, rs, stmt);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableMap;
    }

    private void close(Connection conn, ResultSet rs, PreparedStatement stmt) {
        // 关闭记录集
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 关闭声明
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 关闭连接对象
        if (conn != null) {
            try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private PreparedStatement getPreparedStatement(Connection conn, String sql) {
        PreparedStatement stat = null;
        try {
            stat = conn.prepareStatement(sql);
        } catch (Exception e) {
            log.error("创建PreparedStatement失败！", e);
        }
        return stat;
    }

    public DriverManagerDataSource getDataSource(DbConfig dbConfig) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        StringBuilder url = new StringBuilder();
        url.append("jdbc:mysql://");
        url.append(dbConfig.getIp()).append(":");
        url.append(dbConfig.getPort()).append("/");
        url.append(dbConfig.getSchema()).append("?");
        url.append("characterEncoding=").append(dbConfig.getEncode());
        dataSource.setUrl(url.toString());
        dataSource.setUsername(dbConfig.getUserName());
        dataSource.setPassword(dbConfig.getPwd());
        return dataSource;
    }
}
